package com.jcbase.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jcbase.controller.sys.UserController;
import com.jcbase.core.pojo.Obj;
import com.jcbase.core.pojo.User;
import com.jcbase.model.SysObj;

public class ImportExcelUtil {

	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 * 
	 * @param in,fileName
	 * @return
	 * @throws IOException
	 */
	public List<List<String>> getBankListByExcel(InputStream in, String fileName) throws Exception {
		List<List<String>> list = null;

		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		int paramsRow= 0;
		list = new ArrayList<List<String>>();
		// 遍历Excel中所有的sheet
		int s = work.getNumberOfSheets();
		List<String> params = new ArrayList<String>();
		sheet = work.getSheetAt(0);
		if (sheet != null) {
			// 遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null || row.getFirstCellNum() == j) {
					for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
						cell = row.getCell(y);
						if(cell.toString().indexOf("params")>=0){
							
							params.add(cell.toString().split("_")[1]);
							if(paramsRow==0){
								paramsRow = y;
							}
							
						}
					}
					continue;
				}

				// 遍历所有的列
				List<String> li = new ArrayList<String>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					if( y>=paramsRow ){
						li.add(params.get(y-paramsRow)+"="+this.getCellValue(cell).toString());
					}else{
						li.add(this.getCellValue(cell).toString());
					}
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}

	
	public List<List<String>> getObjListByExcel(InputStream in, String fileName) throws Exception {
		List<List<String>> list = null;

		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		list = new ArrayList<List<String>>();
		// 遍历Excel中所有的sheet
		int s = work.getNumberOfSheets();
		sheet = work.getSheetAt(0);
		if (sheet != null) {
			// 遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null || row.getFirstCellNum() == j) {
					continue;
				}

				// 遍历所有的列
				List<String> li = new ArrayList<String>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(this.getCellValue(cell).toString());
					
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public String getCellValue(Cell cell) {
		String value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}

	private HSSFWorkbook workbook = null;

	/**
	 * 判断文件是否存在
	 * 
	 * @param filePath
	 *            文件路径
	 * @return
	 */
	public boolean fileExist(String filePath) {
		boolean flag = false;
		File file = new File(filePath);
		flag = file.exists();
		return flag;
	}

	/**
	 * 判断文件的sheet是否存在
	 * 
	 * @param filePath
	 *            文件路径
	 * @param sheetName
	 *            表格索引名
	 * @return
	 */
	public boolean sheetExist(String filePath, String sheetName) {
		boolean flag = false;
		File file = new File(filePath);
		if (file.exists()) { // 文件存在
			// 创建workbook
			try {
				workbook = new HSSFWorkbook(new FileInputStream(file));
				// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
				HSSFSheet sheet = workbook.getSheet(sheetName);
				if (sheet != null)
					flag = true;
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else { // 文件不存在
			flag = false;
		}
		return flag;
	}

	/**
	 * 创建新Sheet并写入第一行数据
	 * 
	 * @param filePath
	 *            excel的路径
	 * @param sheetName
	 *            要创建的表格索引
	 * @param titleRow
	 *            excel的第一行即表格头
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	public void createSheet(String filePath, String sheetName, String titleRow[])
			throws FileNotFoundException, IOException {
		FileOutputStream out = null;
		File excel = new File(filePath); // 读取文件
		FileInputStream in = new FileInputStream(excel); // 转换为流
		workbook = new HSSFWorkbook(in); // 加载excel的 工作目录

		workbook.createSheet(sheetName); // 添加一个新的sheet
		// 添加表头
		Row row = workbook.getSheet(sheetName).createRow(0); // 创建第一行
		try {
			for (int i = 0; i < titleRow.length; i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(titleRow[i]);
			}
			out = new FileOutputStream(filePath);
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 创建新excel.
	 * 
	 * @param filePath
	 *            excel的路径
	 * @param sheetName
	 *            要创建的表格索引
	 * @param titleRow
	 *            excel的第一行即表格头
	 */
	public void createExcel(String filePath, String sheetName, String titleRow[]) {
		// 创建workbook
		workbook = new HSSFWorkbook();
		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		workbook.createSheet(sheetName);
		// 新建文件
		FileOutputStream out = null;
		try {
			// 添加表头
			Row row = workbook.getSheet(sheetName).createRow(0); // 创建第一行
			for (int i = 0; i < titleRow.length; i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(titleRow[i]);
			}
			out = new FileOutputStream(filePath);
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 往excel中写入.
	 * 
	 * @param filePath
	 *            文件路径
	 * @param sheetName
	 *            表格索引
	 * @param object
	 * @throws IOException
	 */
	public String writeUserToExcel(List<User> userList) throws IOException {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("用户表");
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("User_Name");
		cell = row.createCell(1);
		cell.setCellValue("User_Group");
		cell = row.createCell(2);
		cell.setCellValue("comments");

		// 第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
		for (int i = 0; i < userList.size(); i++) {
			HSSFRow row1 = sheet.createRow(i + 1);
			User user = userList.get(i);
			// 创建单元格设值
			row1.createCell(0).setCellValue(user.getUser());
			row1.createCell(1).setCellValue(user.getGroup());
			row1.createCell(2).setCellValue(user.getCommon());
		}
		String path = UserController.class.getClassLoader().getResource("").getPath() + "user.xlsx";
		FileOutputStream fos = new FileOutputStream(path);
		workbook.write(fos);

		return path;
	}

	/**
	 * 往excel中写入.
	 * 
	 * @param filePath
	 *            文件路径
	 * @param sheetName
	 *            表格索引
	 * @param object
	 * @throws IOException
	 */
	public String writeObjToExcel(List<SysObj> objList) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("链接表");
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("名称");
		cell = row.createCell(1);
		cell.setCellValue("主题");
		cell = row.createCell(2);
		cell.setCellValue("分类");
		cell = row.createCell(3);
		cell.setCellValue("链接");

		// 第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
		for (int i = 0; i < objList.size(); i++) {
			HSSFRow row1 = sheet.createRow(i + 1);
			SysObj obj = objList.get(i);
			// 创建单元格设值
			row1.createCell(0).setCellValue(obj.getName());
			row1.createCell(1).setCellValue(obj.getSubject());
			row1.createCell(2).setCellValue(obj.getType());
			row1.createCell(3).setCellValue(obj.getLink());
		}
		String path = UserController.class.getClassLoader().getResource("").getPath() + "link.xlsx";
		FileOutputStream fos = new FileOutputStream(path);
		workbook.write(fos);

		return path;

	}
}